package personal.study.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import personal.study.form.StorageForm;

public class DBStorage {
	private static Log log = LogFactory.getLog(DBStorage.class);
	
	public Collection<StorageForm> queryStorage(Connection con,
			String conditions, int currentPage, int rowsPerPage)
			throws Exception {
		Collection<StorageForm> col = new ArrayList<StorageForm>();
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("ProductCode,");
		buffer.append("ProductName,");
		buffer.append("ProductAmount,");
		buffer.append("ProductPrice,");
		buffer.append("MakeDate,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM Storage WHERE ");
		buffer.append(conditions);
		buffer.append(" LIMIT ");
		buffer.append((currentPage-1)*rowsPerPage);
		buffer.append(",");
		buffer.append(rowsPerPage);

		log.debug(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		StorageForm storageForm = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				storageForm = new StorageForm();
				storageForm.setProductCode(rs.getString("ProductCode"));
				storageForm.setProductName(rs.getString("ProductName"));
				storageForm.setProductAmount(rs.getInt("ProductAmount"));
				storageForm.setProductPrice(rs.getDouble("ProductPrice"));
				storageForm.setMakeDate(rs.getDate("MakeDate"));
				storageForm.setFlag(rs.getString("Flag"));
				storageForm.setRemark(rs.getString("Remark"));
				col.add(storageForm);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}

		return col;
	}
	
	public Collection<StorageForm> queryStorage(Connection con, String conditions)
			throws Exception {
		Collection<StorageForm> col = new ArrayList<StorageForm>();
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("ProductCode,");
		buffer.append("ProductName,");
		buffer.append("ProductAmount,");
		buffer.append("ProductPrice,");
		buffer.append("MakeDate,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM Storage WHERE ");
		buffer.append(conditions);

		log.debug(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		StorageForm storageForm = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				storageForm = new StorageForm();
				storageForm.setProductCode(rs.getString("ProductCode"));
				storageForm.setProductName(rs.getString("ProductName"));
				storageForm.setProductAmount(rs.getInt("ProductAmount"));
				storageForm.setProductPrice(rs.getDouble("ProductPrice"));
				storageForm.setMakeDate(rs.getDate("MakeDate"));
				storageForm.setFlag(rs.getString("Flag"));
				storageForm.setRemark(rs.getString("Remark"));
				col.add(storageForm);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}

		return col;
	}

	public int getCount(Connection con, String conditions) throws Exception {
		int count = 0;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("SELECT COUNT(*) FROM Storage WHERE ");
		buffer.append(conditions);

		log.debug(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
		} catch (Exception e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}
		return count;
	}
	
	public boolean insert(Connection con, StorageForm storageForm) throws Exception {
		boolean result = false;
		
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("INSERT INTO Storage (");
		buffer.append("ProductCode,");
		buffer.append("ProductName,");
		buffer.append("ProductAmount,");
		buffer.append("ProductPrice,");
		buffer.append("MakeDate,");
		buffer.append("Flag,");
		buffer.append("Remark) ");
		
		if(log.isDebugEnabled()){
			StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
			debugBuffer.append(buffer.toString());
			debugBuffer.append("VALUES (");
			debugBuffer.append("'").append(storageForm.getProductCode()).append("',");
			debugBuffer.append("'").append(storageForm.getProductName()).append("',");
			debugBuffer.append("'").append(storageForm.getProductAmount()).append("',");
			debugBuffer.append("'").append(storageForm.getProductPrice()).append("',");
			debugBuffer.append("'").append(storageForm.getMakeDate()).append("',");
			debugBuffer.append("'").append(storageForm.getFlag()).append("',");
			debugBuffer.append("'").append(storageForm.getRemark()).append("')");
			log.debug(debugBuffer.toString());
		}
		
		buffer.append("VALUES (?,?,?,?,?,?,?)");
		
		PreparedStatement ps = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, storageForm.getProductCode());
			ps.setString(2, storageForm.getProductName());
			ps.setInt(3, storageForm.getProductAmount());
			ps.setDouble(4, storageForm.getProductPrice());
			ps.setDate(5, storageForm.getMakeDate());
			ps.setString(6, storageForm.getFlag());
			ps.setString(7, storageForm.getRemark());
			ps.executeUpdate();
			result = true;
		}catch(Exception e){
			log.error(e.getMessage());
			result = false;
		}finally{
			ps.close();
		}
		
		return result;
	}
	
	public StorageForm findByPrimaryKey(Connection con, String storageCode) throws Exception{
		StorageForm storageForm = null;
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("ProductCode,");
		buffer.append("ProductName,");
		buffer.append("ProductAmount,");
		buffer.append("ProductPrice,");
		buffer.append("MakeDate,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM Storage WHERE ");
		
		if(log.isDebugEnabled()){
			StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
			debugBuffer.append(buffer.toString());
			debugBuffer.append("ProductCode=");
			debugBuffer.append("'").append(storageCode).append("'");
			log.debug(debugBuffer.toString());
		}
		
		buffer.append("ProductCode=?");
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, storageCode);
			rs = ps.executeQuery();
			if(rs.next()){
				storageForm = new StorageForm();
				storageForm.setProductCode(rs.getString("ProductCode"));
				storageForm.setProductName(rs.getString("ProductName"));
				storageForm
						.setProductAmount(rs.getInt("ProductAmount"));
				storageForm
						.setProductPrice(rs.getDouble("ProductPrice"));
				storageForm.setMakeDate(rs.getDate("MakeDate"));
				storageForm.setFlag(rs.getString("Flag"));
				storageForm.setRemark(rs.getString("Remark"));
			}
		}catch(Exception e){
			log.error(e.getMessage());
		}finally{
			ps.close();
			rs.close();
		}
		return storageForm;
	}
	
	public boolean update(Connection con, StorageForm storageForm) throws Exception{
		boolean result = false;
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("UPDATE Storage SET ");
		buffer.append("ProductName=?,");
		buffer.append("ProductAmount=?,");
		buffer.append("ProductPrice=?,");
		buffer.append("MakeDate=?,");
		buffer.append("Flag=?,");
		buffer.append("Remark=? ");
		
		if(log.isDebugEnabled()){
			
		}
		
		buffer.append("WHERE ");
		buffer.append("ProductCode=?");
		
		PreparedStatement ps = null;
		try{
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, storageForm.getProductName());
			ps.setInt(2, storageForm.getProductAmount());
			ps.setDouble(3, storageForm.getProductPrice());
			ps.setString(4, storageForm.getMakeDate().toString());
			ps.setString(5, storageForm.getFlag());
			ps.setString(6, storageForm.getRemark());
			ps.setString(7, storageForm.getProductCode());
			ps.executeUpdate();
			result = true;
		}catch(Exception e){
			log.error(e.getMessage());
			result = false;
		}finally{
			ps.close();
		}
		return result;
	}
}
